﻿using BLL;
using BLL.Access;
using DevExpress.Web;
using Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Web.Class;

namespace Web.Pages.YongShui._5.ShouFeiGL
{
    public partial class JiaoKuanShuDR : System.Web.UI.Page
    {
        /// <summary>
        ///     数据访问操作类
        /// </summary>
        private readonly SpHelper _spHelper = new SpHelper();
        SqlHelper _SqlHelper = new SqlHelper();
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void UpLoadFile_FileUploadComplete(object sender, FileUploadCompleteEventArgs e)
        {
            e.CallbackData = "操作失败";
            var userid = LoginInfo.CurrentUser.UserID;
            const string UploadDirectory = "/UPLOAD/DXS/CB/"; //图片上传文件夹路径
            if (!UpLoadFile.IsValid)
            {
                
            }
            else
            {
                var fileName = Guid.NewGuid() + Path.GetExtension(UpLoadFile.FileName); //文件名
                var path = Server.MapPath(UploadDirectory);//路径名
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                var filePath = Path.Combine(path, fileName); //文件完整物理路径
                UpLoadFile.SaveAs(filePath);//UpLoadFile控件名
                var dt = ReadExcel(filePath);
                if (dt != null && dt.Rows.Count > 0)
                {
                   // var newdt = _spHelper.P_QSCB_DRSJ2(userid, 0).Tables[0];  
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["起始号码"] != DBNull.Value)
                        {
                            DateTime dtDZ = DateTime.Now;
                            DateTime.TryParse(dr["到账日期"].ToString(), out dtDZ);
                            int iYM = int.Parse(dtDZ.ToString("yyyyMM"));//yyyyMM

                            DateTime dtHX = DateTime.Now;
                            DateTime.TryParse(dr["核销日期"].ToString(), out dtHX);

                            int S_FAPIAOHM = Convert.ToInt32(dr["起始号码"]);

                            _SqlHelper.qs_sf_daozhang(dtDZ, iYM, dtHX, S_FAPIAOHM);
                        }
                    }
                    //Response.Write("<script>alert('导入成功!')</script>");
                    e.IsValid = true;
                    e.CallbackData = dt.Rows.Count.ToString();
                }
            }
        }
        //
        private DataTable ReadExcel(string ExcelFilePath)
        {
            var dt = new DataTable();
            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" +ExcelFilePath + ";Extended Properties=Excel 8.0";//;IMEX=1
            //var strConnection =
            //    "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';data source=" +
            //    ExcelFilePath;
            var Connection = new OleDbConnection(strConnection);//导入数据库
            Connection.Open();
            var CommandText = "";
            var dtsheetName = GetWorkBookName(Connection);
            if (dtsheetName != null && dtsheetName.Rows.Count > 0)
            {
                string sTabName = dtsheetName.Rows[0]["TABLE_NAME"].ToString();
                CommandText = "select * from [" + sTabName + "]";//qs_sf_daozhang/
            }

            else
                return null;
            var comm = new OleDbCommand(CommandText, Connection);

            try
            {
                var da = new OleDbDataAdapter(comm);
                var Ds = new DataSet();
                da.Fill(Ds);
                dt = Ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Excel驱动错误！\n" + ex.Message);
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }

            return dt;
        }
        /// <summary>
        ///     获取Excel中的所有工作簿
        /// </summary>
        /// <param name="Connection">OleDbConnection对象</param>
        /// <returns></returns>
        public static DataTable GetWorkBookName(OleDbConnection Connection)
        {
            DataTable Dt = null;
            try
            {
                if (Connection.State == ConnectionState.Closed) Connection.Open();
                Dt = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            catch (Exception)
            {
            }
            finally
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
            }

            return Dt;
        }
    }
}